import Database from "better-sqlite3";
import path from "path";
import fs from "fs";

// Initialize a persistent SQLite database for user accounts
const DATA_DIR = path.join(process.cwd(), "data");
const DB_PATH = path.join(DATA_DIR, "users.db");

function ensureDatabase() {
  if (!fs.existsSync(DATA_DIR)) {
    fs.mkdirSync(DATA_DIR, { recursive: true });
  }
  const db = new Database(DB_PATH);
  db.pragma("journal_mode = WAL");
  db.prepare(
    `CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      display_name TEXT,
      created_at TEXT NOT NULL
    )`
  ).run();
  db.prepare(
    `CREATE TABLE IF NOT EXISTS comments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      pathname TEXT NOT NULL,
      user_id INTEGER NOT NULL,
      content TEXT NOT NULL,
      created_at TEXT NOT NULL,
      FOREIGN KEY(user_id) REFERENCES users(id)
    )`
  ).run();
  return db;
}

let singletonDb;
export function getDb() {
  if (!singletonDb) {
    singletonDb = ensureDatabase();
  }
  return singletonDb;
}

export function findUserByEmail(email) {
  const db = getDb();
  return db.prepare("SELECT id, email, password_hash, display_name, created_at FROM users WHERE email = ?").get(email);
}

export function createUser({ email, passwordHash, displayName }) {
  const db = getDb();
  const now = new Date().toISOString();
  const stmt = db.prepare(
    "INSERT INTO users (email, password_hash, display_name, created_at) VALUES (?, ?, ?, ?)"
  );
  const info = stmt.run(email, passwordHash, displayName || null, now);
  return { id: info.lastInsertRowid, email, display_name: displayName || null, created_at: now };
}

export function getUserById(id) {
  const db = getDb();
  return db.prepare("SELECT id, email, display_name, created_at FROM users WHERE id = ?").get(id);
}


export function listCommentsByPathname(pathname) {
  const db = getDb();
  return db.prepare(
    `SELECT c.id, c.pathname, c.content, c.created_at,
            u.id as user_id, u.email as user_email, u.display_name as user_display_name
       FROM comments c
       JOIN users u ON u.id = c.user_id
      WHERE c.pathname = ?
      ORDER BY c.id DESC`
  ).all(pathname);
}

export function createComment({ pathname, userId, content }) {
  const db = getDb();
  const now = new Date().toISOString();
  const stmt = db.prepare(
    `INSERT INTO comments (pathname, user_id, content, created_at) VALUES (?, ?, ?, ?)`
  );
  const info = stmt.run(pathname, userId, content, now);
  return { id: info.lastInsertRowid, pathname, user_id: userId, content, created_at: now };
}


